USE SlidingWindow;
GO

CREATE PROCEDURE dbo.GetPartitionInfo
AS
BEGIN
	SET NOCOUNT ON;
	
	-- browse the FactTable table
	SELECT
		ID1,
		ID2,
		FactDate,
		Measure1,
		Measure2
	FROM dbo.FactTable;

	-- show the boundaries for the pfDateRange
	SELECT
		prv.function_id,
		prv.boundary_id,
		CAST(prv.value AS INT) AS partition_boundary
	FROM sys.partition_functions AS pf
	INNER JOIN sys.partition_range_values AS prv ON prv.function_id = pf.function_id
	WHERE pf.name = 'pfDateRange';

	-- show the table partitions and rows inserted in the partitions
	SELECT
		p.partition_number,
		fg.name AS [filegroup_name],
		p.rows,
		au.total_pages,
		au.used_pages
	FROM sys.partitions p
	INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
	INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
	WHERE p.object_id = OBJECT_ID('dbo.FactTable')
	AND p.index_id < 2
	ORDER BY p.partition_number ASC;
END
GO